<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class M_persetujuan_jaminan extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get() {
		$sql = 'SELECT klaim.kd_surat_jaminan, nama, jk, alamat, jenis_kunjungan, 
		tgl_surat, nickname, status_jaminan, id_petugas_loket FROM surat_jaminan 
		INNER JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		INNER JOIN klaim ON surat_jaminan.kd_surat_jaminan = klaim.kd_surat_jaminan
		INNER JOIN master_jenis_kunjungan ON surat_jaminan.id_jenis_kunjungan = master_jenis_kunjungan.id_jenis_kunjungan
		INNER JOIN rc_users ON klaim.id_petugas_loket = rc_users.id
		WHERE klaim.jenis = "baru" AND tgl_surat = CURDATE() 
		ORDER BY klaim.tgl_surat, surat_jaminan.kd_surat_jaminan DESC';
		return $sql;
	}

	function get_cari($keyword,$jenis_kunjungan,$status,$petugas) {
		if($jenis_kunjungan != ''){
			$qry_jenis_kunjungan = "AND surat_jaminan.id_jenis_kunjungan = '".$jenis_kunjungan."'";
		}else{
			$qry_jenis_kunjungan = "";
		}
		if($status != ''){
			$qry_status = "AND status_jaminan = '".$status."'";
		}else{
			$qry_status = "";
		}
		if($petugas != ''){
			$qry_petugas = "AND id_petugas_loket = '".$petugas."'";
		}else{
			$qry_petugas = "";
		}
		$sql = 'SELECT klaim.kd_surat_jaminan, nama, jk, alamat, jenis_kunjungan, 
		tgl_surat, nickname, status_jaminan, id_petugas_loket FROM surat_jaminan 
		LEFT JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		LEFT JOIN penduduk ON peserta.nik = penduduk.nik
		LEFT JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		LEFT JOIN klaim ON surat_jaminan.kd_surat_jaminan = klaim.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan ON surat_jaminan.id_jenis_kunjungan = master_jenis_kunjungan.id_jenis_kunjungan
		LEFT JOIN rc_users ON klaim.id_petugas_loket = rc_users.id
		WHERE klaim.jenis = "baru" 
		'.$qry_petugas.'
		AND (surat_jaminan.kd_surat_jaminan like "%'.$keyword.'%" 
		OR penduduk.nama like "%'.$keyword.'%" 
		OR penduduk.nik like "%'.$keyword.'%")
		'.$qry_jenis_kunjungan.'
		'.$qry_status.'
		ORDER BY klaim.tgl_surat, surat_jaminan.kd_surat_jaminan DESC';
		return $sql;
	}

	function get_cari_tanggal($keyword,$jenis_kunjungan,$status,$tgl_awal,$tgl_akhir,$petugas) {
		if($jenis_kunjungan != ''){
			$qry_jenis_kunjungan = "AND surat_jaminan.id_jenis_kunjungan = '".$jenis_kunjungan."'";
		}else{
			$qry_jenis_kunjungan = "";
		}
		if($status != ''){
			$qry_status = "AND status_jaminan = '".$status."'";
		}else{
			$qry_status = "";
		}
		if($petugas != ''){
			$qry_petugas = "AND id_petugas_loket = '".$petugas."'";
		}else{
			$qry_petugas = "";
		}
		$sql = 'SELECT klaim.kd_surat_jaminan, nama, jk, alamat, jenis_kunjungan, 
		tgl_surat, nickname, status_jaminan, id_petugas_loket FROM surat_jaminan 
		LEFT JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		LEFT JOIN penduduk ON peserta.nik = penduduk.nik
		LEFT JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		LEFT JOIN klaim ON surat_jaminan.kd_surat_jaminan = klaim.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan ON surat_jaminan.id_jenis_kunjungan = master_jenis_kunjungan.id_jenis_kunjungan
		LEFT JOIN rc_users ON klaim.id_petugas_loket = rc_users.id
		WHERE klaim.jenis = "baru"
		'.$qry_petugas.'
		AND (surat_jaminan.kd_surat_jaminan like "%'.$keyword.'%" 
		OR penduduk.nama like "%'.$keyword.'%" 
		OR penduduk.nik like "%'.$keyword.'%")
		'.$qry_jenis_kunjungan.'
		'.$qry_status.'
		AND tgl_surat BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
		ORDER BY klaim.tgl_surat, surat_jaminan.kd_surat_jaminan DESC';
		return $sql;
	}

	function get_export() {
		$sql = 'SELECT * FROM surat_jaminan';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function save_surat_jaminan($dataInsert) {
		$query = $this->db->insert('surat_jaminan', $dataInsert);
		return $query;
	}

	function save_diagnosa($dataInsert) {
		$query = $this->db->insert('jaminan_diagnosa', $dataInsert);
		return $query;
	}

	function save_lampiran($dataInsert) {
		$query = $this->db->insert('jaminan_lampiran', $dataInsert);
		return $query;
	}

	function save_klaim($dataInsert) {
		$query = $this->db->insert('klaim', $dataInsert);
		return $query;
	}

	function get_by($params) {
		$sql = 'SELECT surat_jaminan.kd_surat_jaminan, id_jenis_kunjungan, nomer_surat, tgl_surat, ppk,
		penduduk.nik, nama, jk , keluarga.alamat, kelas_perawatan, surat_jaminan.keterangan,
		keterangan_verifikator, status_jaminan, ttd, first_name, last_name, nip, diagnosa_lain,
		(YEAR(tgl_surat)-YEAR(tgl_lahir)) AS umur FROM surat_jaminan
		INNER JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		INNER JOIN master_kelas_perawatan ON surat_jaminan.id_kelas = master_kelas_perawatan.id_kelas_perawatan
		INNER JOIN ppk ON surat_jaminan.kd_ppk = ppk.kd_ppk
		INNER JOIN klaim ON surat_jaminan.kd_surat_jaminan = klaim.kd_surat_jaminan
		LEFT JOIN rc_users ON surat_jaminan.id_verifikator = rc_users.id
		WHERE surat_jaminan.kd_surat_jaminan = ? LIMIT 1';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_status_by($params) {
		$sql = 'SELECT kd_surat_jaminan, status_jaminan, keterangan_verifikator, nama FROM surat_jaminan
		INNER JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		WHERE kd_surat_jaminan = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_diagnosa_by($params) {
		$sql = 'SELECT * FROM jaminan_diagnosa 
		INNER JOIN master_diagnosa ON jaminan_diagnosa.kd_diagnosa = master_diagnosa.kd_diagnosa
		WHERE kd_surat_jaminan = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_lampiran_by($params) {
		$sql = 'SELECT * FROM jaminan_lampiran WHERE kd_surat_jaminan = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function update($kd_surat_jaminan, $dataUpdate) {
		$this->db->where('kd_surat_jaminan', $kd_surat_jaminan);
		$update = $this->db->update('surat_jaminan', $dataUpdate);
		return $update;
	}

	function delete($kd_surat_jaminan) {
		$this->db->where('kd_surat_jaminan', $kd_surat_jaminan);
		$delete = $this->db->delete('surat_jaminan');
		return $delete;
	}

	function get_ppk() {
		$sql = 'SELECT * FROM ppk WHERE id_jenis_ppk <> 1';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_rujukan() {
		$sql = 'SELECT * FROM ppk';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_jenis_kunjungan() {
		$sql = 'SELECT * FROM master_jenis_kunjungan';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_peserta($kd_peserta) {
		$sql = 'SELECT nama, alamat, tempat_lahir, tgl_lahir FROM peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		WHERE kd_peserta = ?';
		$query = $this->db->query($sql,$kd_peserta);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_kelas_perawatan() {
		$sql = 'SELECT * FROM master_kelas_perawatan';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_diagnosa() {
		$sql = 'SELECT * FROM master_diagnosa ORDER BY diagnosa';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_kode_max($kode) {
		$sql = 'SELECT max(kd_surat_jaminan) as max FROM surat_jaminan 
		WHERE kd_surat_jaminan LIKE "'.$kode.'%"';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['max'];
	}

	function get_nomer_max($jenis_kunjungan) {
		$sql = 'SELECT max(nomer_surat) as max FROM surat_jaminan 
		WHERE id_jenis_kunjungan = ?';
		$query = $this->db->query($sql, $jenis_kunjungan);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['max'];
	}

	function get_klaim_max($kode) {
		$sql = 'SELECT max(kd_klaim) as max FROM klaim 
		WHERE kd_klaim LIKE "'.$kode.'%"';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['max'];
	}

	function get_petugas() {
		$sql = 'SELECT rc_users.id, nickname FROM rc_users 
		INNER JOIN rc_users_groups ON rc_users.id = rc_users_groups.user_id
		WHERE group_id = "5"';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}
}
